package com.sondertara.joya.core.query;


import com.sondertara.common.text.StringUtils;
import com.sondertara.joya.core.JoyaJdbc;
import com.sondertara.joya.core.params.DeployUnitParam;
import com.sondertara.joya.core.po.AppCompPo;
import com.sondertara.joya.core.po.AppDuCopyPo;
import com.sondertara.joya.core.po.AppDuPo;
import com.sondertara.joya.core.po.AppNasPo;
import com.sondertara.joya.core.po.FbFlwTicketRelPo;
import com.sondertara.joya.core.po.FlwTMainFlowPo;
import com.sondertara.joya.core.po.NAppDuPo;
import com.sondertara.joya.core.query.criterion.SelectCriterion;
import com.sondertara.joya.core.query.pagination.FieldParam;
import com.sondertara.joya.core.query.pagination.JoyaPageConvert;
import com.sondertara.joya.core.query.pagination.PageQueryParam;
import com.sondertara.joya.core.query.pagination.SearchParam;
import com.sondertara.joya.utils.SqlUtils;

import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Test;

import java.util.ArrayList;
import java.util.List;
import java.util.function.UnaryOperator;

public class NativeSqlQueryTest {


    @BeforeAll
    public static void before() {
        JoyaJdbc.config().setPackageToScan(new String[]{"com.sondertara.joya.core.po"});
    }


    @Test
    public void testBuilder() {

        NativeSqlQuery build = NativeSqlQuery.builder().select().from(join -> join.join(AppCompPo::getAppId, AppDuPo::getAppId).leftJoin(AppDuCopyPo::getAppId, AppNasPo::getId)).build();

        System.out.println(build.toFormattedSql());

    }

    @Test
    public void testJoin() {
        NativeSqlQuery query = NativeSqlQuery.builder()
                .alias("t1.ticketNo supplementNo")
                .select()
                .from(j -> j.join(FlwTMainFlowPo::getId, FbFlwTicketRelPo::getFlowId))
                .where(w -> w.eq(FbFlwTicketRelPo::getTicketId, "ffff"))
                .limit(5)
                .build();
        System.out.println(query.toSql());
        System.out.println(query.getParams());

    }

    @Test
    public void testJoinMore() {
        NativeSqlQuery query = NativeSqlQuery.builder().select().from(j -> j.join(NAppDuPo::getDuId, AppDuPo::getDuId).join(NAppDuPo::getAppSysCode,AppCompPo::getAppSysCode)).build();
        System.out.println(query.toSql());
        System.out.println(query.getParams());

    }

    @Test
    public void testFromRaw() {

        {
            NativeSqlQuery query = NativeSqlQuery.builder().select()
                    .from(AppDuPo.class)
                    .having("count(*) >1")
                    .build();

            System.out.println(query.toFormattedSql());

            System.out.println("===============================================");
        }

        {
            NativeSqlQuery query = NativeSqlQuery.builder().select()
                    .from("APP_COMP", "APP_DU")
                    .where(w -> w.eq(AppCompPo::getAppId, "111"))
                    .build();

            System.out.println(query.toFormattedSql());
        }
    }

    @Test
    public void testFromClass() {
        {
            NativeSqlQuery query = NativeSqlQuery.builder()
                    .select()
                    .from(AppCompPo.class, AppDuPo.class)
                    .build();

            System.out.println(query.toFormattedSql());
        }

        {
            NativeSqlQuery query = NativeSqlQuery.builder()
                    .alias("t0.app_id AA").select()
                    .from(AppCompPo.class, AppDuPo.class)
                    .build();

            System.out.println(query.toFormattedSql());
        }
    }


    @Test
    public void testWhereClass() {
        {
            NativeSqlQuery query = NativeSqlQuery.builder()
                    .select()
                    .from(AppCompPo.class, AppDuPo.class)
                    .where(w -> w.eq(AppCompPo::getAppId, AppDuPo::getAppId)
                            .orNested(or -> or
                                    .lt(AppCompPo::getCname, "222")
                                    .lt(AppCompPo::getAppLevel, "A")))
                    .build();

            System.out.println(query.toFormattedSql());
            System.out.println(query.getParams());
        }


    }

    @Test
    public void testQuery() {
        DeployUnitParam deployUnitParam = new DeployUnitParam();
        deployUnitParam.setAppSysCode("W0341A");
        NativeSqlQuery nativeSqlQuery = NativeSqlQuery.builder().select()
                .from(NAppDuPo.class)
                .where(w -> {
                    w.eq(NAppDuPo::getIsActive, "Y");
                    if (StringUtils.isNotBlank(deployUnitParam.getAppSysCode())) {
                        w.eq(NAppDuPo::getAppSysCode, deployUnitParam.getAppSysCode());

                    }
                    if (StringUtils.isNotBlank(deployUnitParam.getDuName())) {
                        w.eq(NAppDuPo::getDuName, deployUnitParam.getDuName());

                    }
                    if (null != deployUnitParam.getDuId()) {
                        w.eq(NAppDuPo::getDuId, deployUnitParam.getDuId());

                    }
                    if (StringUtils.isNotBlank(deployUnitParam.getDuTypeCode())) {
                        w.eq(NAppDuPo::getDuTypeCode, deployUnitParam.getDuTypeCode());

                    }
                    if (StringUtils.isNotBlank(deployUnitParam.getDuCmdbId())) {
                        w.eq(NAppDuPo::getCmdbDuId, deployUnitParam.getDuCmdbId());

                    }
                    if (null != deployUnitParam.getDatacenterId()) {
                        w.eq(NAppDuPo::getDatacenterId, deployUnitParam.getDatacenterId());

                    }
                    return w;

                }).build();

        String sql = nativeSqlQuery.toSql();
        List<Object> params = nativeSqlQuery.getParams();
        System.out.println(sql);
        System.out.println(params);
    }


    @Test
    public void testWhereRaw() {
        {
            JoyaJdbc.config().setColumnUppercase(true);
            NativeSqlQuery query = NativeSqlQuery.builder()
                    .select()
                    .from(AppCompPo.class, AppDuPo.class)
                    .where(w -> w.eq("t0.app_id", "111").addCondition("t0.APP_SYS_CODE is not null"))
                    .build();

            System.out.println(query.toFormattedSql());
        }


    }

    @Test
    public void testSelectUn() {
        {
            JoyaJdbc.config().setColumnUppercase(true);
            NativeSqlQuery query = NativeSqlQuery.builder()
                    .alias("t0.APP_ID BBB")
                    .select((UnaryOperator<SelectCriterion>) s -> s.add(AppCompPo::getAppId).add(AppDuPo::getDuId))
                    .from(AppCompPo.class, AppDuPo.class)
                    .where(w -> w.eq("t0.app_id", "111").addCondition("t0.APP_SYS_CODE is not null"))
                    .groupBy(AppCompPo::getAppId)
                    .build();

            System.out.println(query.toFormattedSql());
        }

        {
            JoyaJdbc.config().setColumnUppercase(true);
            NativeSqlQuery query = NativeSqlQuery.builder()
                    .alias("t0.APP_ID APP_ID")
                    .select()
                    .from(AppCompPo.class, AppDuPo.class)
                    .where(w -> w.eq("t0.app_id", "111").addCondition("t0.APP_SYS_CODE is not null"))
                    .groupBy(AppCompPo::getAppId)
                    .build();

            System.out.println(query.toFormattedSql());
        }


    }


    @Test
    public void testPage() {
        PageQueryParam pageQueryParam = new PageQueryParam();
        pageQueryParam.addSearchParam("t0.app_id", "111", FieldParam.Operator.EQ);
        pageQueryParam.addSearchParam("t0.cname", "111", FieldParam.Operator.EQ);
        PageQueryParam.Nested nested = new PageQueryParam.Nested();
        List<SearchParam> and = new ArrayList<>();

        and.add(new SearchParam("t0.app_level", "A", FieldParam.Operator.EQ));
        and.add(new SearchParam("t0.en_short_name", "A", FieldParam.Operator.EQ));
        nested.setOr(and);
        pageQueryParam.setNested(nested);

        NativeSqlQuery sqlQuery = JoyaPageConvert.create()
                .queryParam(pageQueryParam)
                .from(AppCompPo.class)
                .build();
        System.out.println(sqlQuery.toFormattedSql());


    }

    @Test
    public void wrapColumnTest() {
        ColumnPair columnPair = SqlUtils.warpColumn("t0.rts,t1.tgg");
        ColumnPair columnPair1 = SqlUtils.warpColumn("t1.ORG_CODE AS orgCode,t1.ORG_NAME AS orgName,count(t0.id) AS appNum");
        ColumnPair columnPair2 = SqlUtils.warpColumn("t0.*,t1.secureNode AS syncSecureNode");
        ColumnPair columnPair3 = SqlUtils.warpColumn("t0.rts tkjg");
        System.out.println(columnPair);
        System.out.println(columnPair1);
        System.out.println(columnPair2);
        System.out.println(columnPair3);

    }


    @Test
    public void testRawSql() {
        NativeSqlQuery query = NativeSqlQuery.builder()
                .select()
                .from("admin_dic t1")
                .where(w -> w.eq("t1.id", 2))
                .orderBy("t1.updateTime desc")
                .build();
        System.out.println(query);
    }

}
